package veiculo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import util.Conexao;

public class RepositorioVeiculoBanco implements IVeiculo {

	private Connection con;
	@Override
	public void adicionar(Veiculo veiculo) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "insert into veiculo (placa,nome,\"valorDia\", \"valorKm\", kilometragem,disponivel,\"idMarca\")"
				+"values (?,?, ?,?,?,?,?)";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, veiculo.getPlaca());
		ps.setString(2,veiculo.getNome());
		ps.setDouble(3, veiculo.getValorDia());
		ps.setDouble(4, veiculo.getValorKm());
		ps.setDouble(5, veiculo.getKilometragem());
		ps.setInt(6, veiculo.getDisponivel());
		ps.setInt(7, veiculo.getMarca().getId());
		ps.execute();
		
	}

	@Override
	public void editar(Veiculo veiculo) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "update veiculo set placa = ?, nome = ?, \"valorDia\" = ?,\"valorKm\" = ?,kilometragem = ?, disponivel = ?, \"idMarca\" = ? where id = ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, veiculo.getPlaca());
		ps.setString(2,veiculo.getNome());
		ps.setDouble(3, veiculo.getValorDia());
		ps.setDouble(4, veiculo.getValorKm());
		ps.setDouble(5, veiculo.getKilometragem());
		ps.setInt(6, veiculo.getDisponivel());
		ps.setInt(7, veiculo.getMarca().getId());
		ps.setInt(8, veiculo.getId());
		ps.execute();
	}

	@Override
	public void remover(int idVeiculo) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "delete from veiculo where id = ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idVeiculo);
		ps.execute();
		
	}

	@Override
	public int existe(int idVeiculo) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "select * from veiculo where id = ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idVeiculo);
		ps.execute();
		int res = 0;
		if(ps.getResultSet().next()){
			res = 1;
		}
		return res;
	}

	@Override
	public Veiculo pesquisar(int idVeiculo) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "select * from veiculo where id = ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idVeiculo);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		Veiculo v = null;
		while(rs.next()){
			v = new Veiculo();
			v.setId(rs.getInt("id"));
			v.setPlaca(rs.getString("placa"));
			v.setNome(rs.getString("nome"));
			v.setValorDia(rs.getDouble("valorDia"));
			v.setValorKm(rs.getDouble("valorKm"));
			v.setKilometragem(rs.getDouble("kilometragem"));
			v.setDisponivel(rs.getInt("disponivel"));
			v.getMarca().setId(rs.getInt("idMarca"));
		}
		
		return v;
	}

	@Override
	public ArrayList<Veiculo> listarPorPlaca(String placa) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "select * from veiculo where lower(placa) like lower(?) || '%';";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, placa);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		ArrayList<Veiculo> vs = new ArrayList<Veiculo>();
		while(rs.next()){
			Veiculo v = new Veiculo();
			v.setId(rs.getInt("id"));
			v.setPlaca(rs.getString("placa"));
			v.setNome(rs.getString("nome"));
			v.setValorDia(rs.getDouble("valorDia"));
			v.setValorKm(rs.getDouble("valorKm"));
			v.setKilometragem(rs.getDouble("kilometragem"));
			v.setDisponivel(rs.getInt("disponivel"));
			v.getMarca().setId(rs.getInt("idMarca"));
			vs.add(v);
		}
		
		return vs;
	}

	
	@Override
	public ArrayList<Veiculo> listar() throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("select * from veiculo");
		ArrayList<Veiculo> vs = new ArrayList<Veiculo>();
		while(rs.next()){
			Veiculo v = new Veiculo();
			v.setId(rs.getInt("id"));
			v.setPlaca(rs.getString("placa"));
			v.setNome(rs.getString("nome"));
			v.setValorDia(rs.getDouble("valorDia"));
			v.setValorKm(rs.getDouble("valorKm"));
			v.setKilometragem(rs.getDouble("kilometragem"));
			v.setDisponivel(rs.getInt("disponivel"));
			v.getMarca().setId(rs.getInt("idMarca"));
			vs.add(v);
		}
		
		return vs;
	}

	@Override
	public ArrayList<Veiculo> listarDisponiveis() throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("select * from veiculo where disponivel = 1;");
		ArrayList<Veiculo> vs = new ArrayList<Veiculo>();
		while(rs.next()){
			Veiculo v = new Veiculo();
			v.setId(rs.getInt("id"));
			v.setPlaca(rs.getString("placa"));
			v.setNome(rs.getString("nome"));
			v.setValorDia(rs.getDouble("valorDia"));
			v.setValorKm(rs.getDouble("valorKm"));
			v.setKilometragem(rs.getDouble("kilometragem"));
			v.setDisponivel(rs.getInt("disponivel"));
			v.getMarca().setId(rs.getInt("idMarca"));
			vs.add(v);
		}
		
		return vs;
	}

	@Override
	public ArrayList<Veiculo> listar(int idMarca) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "select * from veiculo where \"idMarca\" = ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idMarca);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		ArrayList<Veiculo> vs = new ArrayList<Veiculo>();
		while(rs.next()){
			Veiculo v = new Veiculo();
			v.setId(rs.getInt("id"));
			v.setPlaca(rs.getString("placa"));
			v.setNome(rs.getString("nome"));
			v.setValorDia(rs.getDouble("valorDia"));
			v.setValorKm(rs.getDouble("valorKm"));
			v.setKilometragem(rs.getDouble("kilometragem"));
			v.setDisponivel(rs.getInt("disponivel"));
			v.getMarca().setId(rs.getInt("idMarca"));
			vs.add(v);
		}
		
		return vs;
	}

	@Override
	public ArrayList<Veiculo> listarDisponiveis(int idMarca) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "select * from veiculo where \"idMarca\" = ? and disponivel = 1;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idMarca);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		ArrayList<Veiculo> vs = new ArrayList<Veiculo>();
		while(rs.next()){
			Veiculo v = new Veiculo();
			v.setId(rs.getInt("id"));
			v.setPlaca(rs.getString("placa"));
			v.setNome(rs.getString("nome"));
			v.setValorDia(rs.getDouble("valorDia"));
			v.setValorKm(rs.getDouble("valorKm"));
			v.setKilometragem(rs.getDouble("kilometragem"));
			v.setDisponivel(rs.getInt("disponivel"));
			v.getMarca().setId(rs.getInt("idMarca"));
			vs.add(v);
		}
		
		return vs;
	}

	@Override
	public ArrayList<Veiculo> listarPorNomeTipo(String nomeTipo) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "select v.* from veiculo v, marca m, tipo t where v.\"idMarca\" = m.id " +
				"and m.\"idTipo\" = t.id and lower(t.nome) like lower(?) || '%';";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, nomeTipo);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		ArrayList<Veiculo> vs = new ArrayList<Veiculo>();
		while(rs.next()){
			Veiculo v = new Veiculo();
			v.setId(rs.getInt("id"));
			v.setPlaca(rs.getString("placa"));
			v.setNome(rs.getString("nome"));
			v.setValorDia(rs.getDouble("valorDia"));
			v.setValorKm(rs.getDouble("valorKm"));
			v.setKilometragem(rs.getDouble("kilometragem"));
			v.setDisponivel(rs.getInt("disponivel"));
			v.getMarca().setId(rs.getInt("idMarca"));
			vs.add(v);
		}
		
		return vs;
	}

	@Override
	public ArrayList<Veiculo> listarPorNomeMarca(String nomeMarca) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "select v.* from veiculo v, marca m where m.id = v.\"idMarca\" and"+
				" lower(m.nome) like lower(?) || '%';";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, nomeMarca);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		ArrayList<Veiculo> vs = new ArrayList<Veiculo>();
		while(rs.next()){
			Veiculo v = new Veiculo();
			v.setId(rs.getInt("id"));
			v.setPlaca(rs.getString("placa"));
			v.setNome(rs.getString("nome"));
			v.setValorDia(rs.getDouble("valorDia"));
			v.setValorKm(rs.getDouble("valorKm"));
			v.setKilometragem(rs.getDouble("kilometragem"));
			v.setDisponivel(rs.getInt("disponivel"));
			v.getMarca().setId(rs.getInt("idMarca"));
			vs.add(v);
		}
		
		return vs;
	}

	@Override
	public ArrayList<Veiculo> listarPorNome(String nome) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "select * from veiculo where lower(nome) like lower(?) || '%';";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, nome);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		ArrayList<Veiculo> vs = new ArrayList<Veiculo>();
		while(rs.next()){
			Veiculo v = new Veiculo();
			v.setId(rs.getInt("id"));
			v.setPlaca(rs.getString("placa"));
			v.setNome(rs.getString("nome"));
			v.setValorDia(rs.getDouble("valorDia"));
			v.setValorKm(rs.getDouble("valorKm"));
			v.setKilometragem(rs.getDouble("kilometragem"));
			v.setDisponivel(rs.getInt("disponivel"));
			v.getMarca().setId(rs.getInt("idMarca"));
			vs.add(v);
		}
		
		return vs;
	}

	@Override
	public void VeiculoDisponivel(int idVeiculo) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "update veiculo set disponivel = 1 where id = ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idVeiculo);
		ps.execute();
	}

	@Override
	public void VeiculoIndisponivel(int idVeiculo) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "update veiculo set disponivel = 0 where id = ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idVeiculo);
		ps.execute();
	}


}
